﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Xml;
using DataAccess;
using DataAccess.Entity;
using Oracle.DataAccess.Client;
using System.Linq;

namespace SkynetOracleAccess
{
    public class OracleDataAccessClient : AbstractDataAccess
    {
        private OracleConnection _mOracleConnection;
        private OracleTransaction _trans;

        #region 构造函数
        /// <summary>
        /// 默认构造函数
        /// </summary>
        public OracleDataAccessClient()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="conString">数据库连接字符串</param>
        public OracleDataAccessClient(string conString)
        {
            this._mOracleConnection = new OracleConnection(conString);
            int start = conString.IndexOf("User Id=", StringComparison.Ordinal) + "User Id=".Length;
            int end = conString.IndexOf(';', start);
            Owner = conString.Substring(start, end - start).ToUpper();
        }
        #endregion

        #region 属性
        /// <summary>
        /// 返回数据库类型枚举成员(DatabaseType)
        /// </summary>
        public override DatabaseType DatabaseType
        {
            get
            {
                return DatabaseType.Oracle;
            }
        }
        /// <summary>
        /// 返回数据库连接接口
        /// </summary>
        public override System.Data.IDbConnection DbConnection
        {
            get
            {
                return _mOracleConnection;
            }
        }
        #endregion

        #region IDataAccess成员
        //重载父类关闭
        //连接池中重复调用时 当调用过于频繁 会造成 在执行SQL语句时 报出 内存不可写 的错误
        //目前处理方法是 当每次关闭时 重新实例化一个连接
        public override void Close()
        {
            base.Close();
            _mOracleConnection = new OracleConnection(this._mOracleConnection.ConnectionString);
        }
        /// <summary>
        /// 开始一个事务
        /// </summary>
        /// <returns>数据库事务接口</returns>
        public override System.Data.IDbTransaction BeginTransaction()
        {
            _trans = this._mOracleConnection.BeginTransaction();
            return _trans;
        }
        /// <summary>
        /// 执行SQL语句,返回DataSet对象.
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">SQL语句的参数集合</param>
        /// <param name="ds">DataSet对象</param>
        /// <param name="tableName">DataSet中表的名称</param>
        /// <returns>DataSet对象</returns>
        public override System.Data.DataSet ExecuteDataset(System.Data.CommandType commandType, string commandText, QueryParameterCollection commandParameters, System.Data.DataSet ds, string tableName)
        {
            base.LogOutPut(new Func<int>(() =>
           {

               OracleCommand cmd = new OracleCommand();
               PrepareCommand(cmd, commandType, commandText, commandParameters);

               OracleDataAdapter da = new OracleDataAdapter(cmd);
               if (Object.Equals(tableName, null) || (tableName.Length < 1))
                   da.Fill(ds);
               else
                   da.Fill(ds, tableName);
               for (int j = 0; j < cmd.Parameters.Count; j++)
               {
                   if (cmd.Parameters[j].Direction == ParameterDirection.Output)
                       commandParameters[j].Value = cmd.Parameters[j].Value;
               }
               cmd.Parameters.Clear();
               if (ds != null && ds.Tables.Count > 0)
               {
                   return ds.Tables[0].Rows.Count;
               }
               else
               {
                   return 0;
               }
           }), "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        public override DataSet ExecuteDataset(string commandText, QueryParameterCollection commandParameters, DataSet ds, int startRecord, int maxRecords, string tableName)
        {
            base.LogOutPut(new Func<int>(() =>
         {

             OracleCommand cmd = new OracleCommand();
             PrepareCommand(cmd, CommandType.Text, commandText, commandParameters);

             OracleDataAdapter da = new OracleDataAdapter(cmd);
             if (Object.Equals(tableName, null) || (tableName.Trim() == string.Empty))
                 da.Fill(ds);
             else
                 da.Fill(ds, startRecord, maxRecords, tableName);
             cmd.Parameters.Clear();
             if (ds != null && ds.Tables.Count > 0)
             {
                 return ds.Tables[0].Rows.Count;
             }
             else
             {
                 return 0;
             }
         }), "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">SQL语句的参数集合</param>
        /// <param name="row">DataRow对象</param>
        /// <param name="sqlStruct">SqlStruct对象,封装Sql语句及参数</param>
        /// <returns>执行SQL语句影响的行数</returns>
        public override int ExecuteNonQuery(System.Data.CommandType commandType, string commandText, QueryParameterCollection commandParameters, System.Data.DataRow row, SqlStruct sqlStruct)
        {
            int tmpValue = 0;

            base.LogOutPut(new Func<int>(() =>
        {

            OracleCommand cmd = new OracleCommand();
            if (sqlStruct == null)
                PrepareCommand(cmd, commandType, commandText, commandParameters);
            else
            {
                for (int i = 0; i < sqlStruct.ParamFields.Length; i++)
                {
                    //由于Oracle 在参数中不支持@_XX的形式，所以统一将此种形式替换成@_OriginalXX来表示原始值
                    if (sqlStruct.ParamFields[i].ParamName.IndexOf('_') != 1)
                        commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName]);
                    else
                    {
                        if (sqlStruct.ParamFields[i].ParamName.IndexOf('@') == 0)
                        {
                            commandParameters.Add(sqlStruct.ParamFields[i].ParamName.Replace("@_", ":Origina"), row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);
                        }
                        else
                        {
                            commandParameters.Add(sqlStruct.ParamFields[i].ParamName.Replace(":_", ":Origina"), row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);
                        }
                    }
                }
                PrepareCommand(cmd, CommandType.Text, sqlStruct.SqlString, commandParameters);
            }
            tmpValue = cmd.ExecuteNonQuery();
            for (int j = 0; j < cmd.Parameters.Count; j++)
            {
                if (cmd.Parameters[j].Direction == ParameterDirection.Output)
                    commandParameters[j].Value = cmd.Parameters[j].Value;
            }
            cmd.Parameters.Clear();
            return tmpValue;
        }), "ExecuteNonQuery", commandText, commandParameters);
            return tmpValue;
        }
        /// <summary>
        /// 执行SQL语句,返回IDataReader接口
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>IDataReader</returns>
        public override System.Data.IDataReader ExecuteReader(System.Data.CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            IDataReader dr = null;
            base.LogOutPut(new Func<int>(() =>
       {

           OracleCommand cmd = new OracleCommand();
           PrepareCommand(cmd, commandType, commandText, commandParameters);
           dr = cmd.ExecuteReader();
           cmd.Parameters.Clear();
           return -1;
       }), "ExecuteReader", commandText, commandParameters);
            return dr;
        }
        /// <summary>
        /// 执行SQL语句,返回object接口
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>object</returns>
        public override object ExecuteScalar(System.Data.CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            object tmpValue = null;
            base.LogOutPut(new Func<int>(() =>
      {

          OracleCommand cmd = new OracleCommand();
          PrepareCommand(cmd, commandType, commandText, commandParameters);
          tmpValue = cmd.ExecuteScalar();
          cmd.Parameters.Clear();
          return -1;
      }), "ExecuteScalar", commandText, commandParameters);
            return tmpValue;
        }
        /// <summary>
        /// 执行SQL语句,返回XmlReader对象
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>XmlReader</returns>
        public override System.Xml.XmlReader ExecuteXmlReader(System.Data.CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            XmlReader reader = null;
            base.LogOutPut(new Func<int>(() =>
       {

           OracleCommand cmd = new OracleCommand();
           PrepareCommand(cmd, commandType, commandText, commandParameters);
           reader = cmd.ExecuteXmlReader();
           cmd.Parameters.Clear();
           return -1;
       }), "ExecuteXmlReader", commandText, commandParameters);
            return reader;
        }

        //public override string GetClob(IDataReader reader, int index)
        //{
        //    OracleDataReader oracleReader = reader as OracleDataReader;
        //    OracleClob clob = oracleReader.GetOracleClob(index);//读取二进制字段

        //    return clob.Value;
        //}
        #endregion
        /// <summary>
        /// 处理DB2Command对象,设置参数等信息.
        /// </summary>
        /// <param name="cmd">DB2Command对象</param>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        private void PrepareCommand(OracleCommand cmd, CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            commandText = commandText.Replace("@", ":");
            if (commandText.IndexOf(":_", StringComparison.Ordinal) >= 0)
            {
                commandText = commandText.Replace(":_", ":Origina");
            }
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            cmd.Connection = this._mOracleConnection;
            cmd.Transaction = _trans;
            cmd.CommandTimeout = 600;
            if ((commandParameters != null) && (commandParameters.Count > 0))
            {
                for (int i = 0; i < commandParameters.Count; i++)
                {
                    cmd.Parameters.Add(commandParameters[i].ParameterName, commandParameters[i].Value);
                    cmd.Parameters[i].Direction = commandParameters[i].Direction;
                    //if (commandParameters[i].DbType == null)
                    //    continue;
                    //if ((int)commandParameters[i].DbType != OracleDbType.IntervalDS.GetHashCode())
                    //    cmd.Parameters[i].OracleDbType = (OracleDbType)commandParameters[i].DbType;

                }
            }
        }




        #region IDataStructure
        public override void FillingTables(List<TableInfo> info)
        {
            string commandText = @"select user_tables.table_name as TABLENAME ,constraint_name as PKNAME
                                                from user_tables 
                                                left join user_constraints t on t.constraint_type='P' and t.table_name = user_tables.table_name
                                                order by user_tables.table_name";
            IDataReader dr = this.ExecuteReader(commandText);
            while (dr.Read())
            {
                info.Add(new TableInfo
                {
                    TableName = dr["TABLENAME"].ToString(),
                    PrimaryKeyName = dr["PKNAME"].ToString()
                });
            }
        }

        public override void FillingColumns(List<ColumnInfo> info)
        {
            string commandText = string.Format(@"WITH TEST AS ( 
                                                select col.column_name,col.table_name,con.constraint_type  
                                                from user_constraints con,  user_cons_columns col 
                                                where con.constraint_name = col.constraint_name 
                                                and con.constraint_type in ('P','R') and con.owner='{0}') 
                                                select  user_tab_columns.TABLE_NAME as TABLENAME,user_tab_columns.COLUMN_NAME as COLUMNNAME, DATA_TYPE as TYPENAME,DATA_LENGTH as LENGTH,case NULLABLE when 'N' then 'Not Null' when 'Y' then 'Null' end ISNULL,
                                                case t.constraint_type when 'P' then '1' else '0' end  PrimaryKey,
                                                case t.constraint_type when 'R' then '1' else '0' end  FOREIGNKEY,user_tab_columns.DATA_SCALE,user_tab_columns.column_id as COLUMNINDEX,DATA_DEFAULT AS DEFAULTVALUE
                                                from   user_tab_columns   
                                                left join TEST t on  t.TABLE_NAME = user_tab_columns.TABLE_NAME and t.COLUMN_NAME = user_tab_columns.COLUMN_NAME
                                                ", Owner);

            IDataReader dr = this.ExecuteReader(commandText);
            while (dr.Read())
            {
                info.Add(new ColumnInfo
                {
                    TableName = dr["TABLENAME"].ToString(),
                    ColumnName = dr["COLUMNNAME"].ToString(),
                    DefaultValue = dr["DEFAULTVALUE"].ToString(),
                    TypeName = dr["TYPENAME"].ToString(),
                    Length = dr["LENGTH"].ToString(),
                    IsPrimaryKey = dr["PRIMARYKEY"].ToString() != string.Empty && dr["PRIMARYKEY"].ToString() != "0",
                    IsNull = dr["ISNULL"].ToString() == "Null",
                    DataScale = dr["DATA_SCALE"].ToString(),
                    ColumnIndex = Convert.ToInt32(dr["COLUMNINDEX"].ToString()),
                    IsForeignKey = dr["FOREIGNKEY"].ToString() != string.Empty && dr["FOREIGNKEY"].ToString() != "0"
                });
            }
        }

        public override void FillingIndexs(List<IndexInfo> info)
        {
            string commandText = string.Format(@" select  i.index_name as INDNAME,i.table_name as TABNAME,column_name as COLNAMES,descend as Sequence,'' as REMARK,
                                                    case UNIQUENESS WHEN 'NONUNIQUE' THEN 0 WHEN 'UNIQUE' THEN 1 END IsUnique
　　　　                                            from user_indexes i,user_ind_columns c
　　　　　                                           where i.index_name=c.index_name
　　　　　                                           and table_owner = '{0}'", Owner);

            IDataReader dr = this.ExecuteReader(commandText);
            while (dr.Read())
            {
                IndexInfo entity = new IndexInfo
                                       {
                                           ColumnName = dr["COLNAMES"].ToString(),
                                           Sequence = dr["Sequence"].ToString(),
                                           TableName = dr["TABNAME"].ToString(),
                                           IndexName = dr["INDNAME"].ToString(),
                                           IsUnique =
                                               dr["IsUnique"].ToString() != string.Empty &&
                                               dr["IsUnique"].ToString() == "1"
                                       };
                info.Add(entity);
            }
        }

        public override void FillingRelations(List<RelationshipInfo> info)
        {
            string commandText = string.Format(@"SELECT c2.table_name as tabname,c1.constraint_name as refkeyname,col1.column_name as colname, 
                                                c2.table_name as reftabname,col2.column_name as refcolname,
                                                case c2.delete_rule when 'CASCADE' then 1 else 0 end CascadingDelete,0 as CascadingUpdate
                                                FROM user_constraints c1
                                                left join user_cons_columns col1 on c1.table_name=col1.table_name and c1.constraint_name=col1.constraint_name
                                                ,user_constraints c2
                                                left join user_cons_columns col2 on c2.table_name=col2.table_name and c2.constraint_name=col2.constraint_name
                                                where c1.constraint_name = c2.r_constraint_name and col1.position = col2.position
                                                and c1.owner='{0}' and c2.owner='{0}'", Owner);

            IDataReader dr = this.ExecuteReader(commandText);
            while (dr.Read())
            {
                info.Add(new RelationshipInfo
                {
                    TableName = dr["TABNAME"].ToString(),
                    RefKeyName = dr["REFKEYNAME"].ToString().Trim(),
                    ColumnName = dr["COLNAME"].ToString().Trim(),
                    RelationTableName = dr["REFTABNAME"].ToString().Trim(),
                    RelationColumnName = dr["REFCOLNAME"].ToString().Trim(),
                    IsCascadingDelete = dr["CascadingDelete"].ToString() != string.Empty && dr["CascadingDelete"].ToString() == "1",
                    IsCascadingUpdate = dr["CascadingUpdate"].ToString() != string.Empty && dr["CascadingUpdate"].ToString() == "1"
                });
            }
        }

        public override void FillingViews(List<ViewInfo> info)
        {

            const string commandText = " SELECT NAME,TEXT as CONTENT,LINE FROM user_source WHERE TYPE = 'VIEW' order by NAME,LINE";

            IDataReader dr = this.ExecuteReader(commandText);
            while (dr.Read())
            {
                info.Add(new ViewInfo
                {
                    ViewName = dr["VIEWNAME"].ToString(),
                    Content = dr["CONTENT"].ToString()
                });
            }
        }

        public override void FillingProcs(List<ProcInfo> info)
        {
            const string commandText = "SELECT NAME as PROCNAME,TEXT as CONTENT,LINE," +
                                       "(select count(0) from user_arguments " +
                                       "where user_source.name =user_arguments.object_name) as  PARAMCOUNT " +
                                       "FROM user_source WHERE TYPE = 'PROCEDURE' ORDER BY NAME,LINE";

            IDataReader dr = this.ExecuteReader(commandText);
            while (dr.Read())
            {
                if (dr["PARAMCOUNT"].ToString() == "0")
                    continue;

                string procName = dr["PROCNAME"].ToString();
                ProcInfo p = info.FirstOrDefault(o => o.ProcName == procName);
                if (p == null)
                {
                    info.Add(new ProcInfo
                                 {
                                     ProcId = dr["PROCNAME"].ToString(),
                                     ProcName = dr["PROCNAME"].ToString(),
                                     Content = dr["CONTENT"].ToString()
                                 });
                }
                else
                {
                    p.Content += dr["CONTENT"].ToString();
                }
            }
        }

        public override void FillingProcParams(List<ProcParamInfo> info)
        {
            const string commandText = "SELECT OBJECT_NAME AS PROCNAME,ARGUMENT_NAME AS PARMNAME,DATA_LENGTH AS LENGTH,DATA_TYPE AS TYPENAME,SEQUENCE AS \"INDEX\", " +
                                       " IN_OUT AS STATUS,DATA_SCALE AS SCALE  "+
                                       " FROM user_arguments " +
                                       " ORDER BY OBJECT_NAME,SEQUENCE";
            IDataReader dr = this.ExecuteReader(commandText);
            while (dr.Read())
            {
                info.Add(new ProcParamInfo
                {
                    ProcId = dr["PROCNAME"].ToString(),
                    ProcParamName = dr["PARMNAME"].ToString(),
                    TypeName = dr["TYPENAME"].ToString(),
                    Length = dr["LENGTH"].ToString(),
                    State = dr["STATUS"].ToString(),
                    ParamIndex = Convert.ToInt32(dr["INDEX"].ToString()),
                    DataScale = dr["SCALE"].ToString(),
                });
            }
        }
        public override void FillingTypeInfos(List<TypeNameInfos> info)
        {
            DataTable table = _mOracleConnection.GetSchema("DataTypes");
            if(table != null && table.Rows.Count > 0)
            {
                info.AddRange(from DataRow row in table.Rows
                              select new TypeNameInfos
                                         {
                                             TypeName = row["TypeName"].ToString()
                                         });
            }
        }
        #endregion

    }
}
